﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using Microsoft.Office.Interop.Excel;
using Log;
namespace PayManage
{
    public partial class frmImportExcel : Form
    {
        private static readonly ILog logger = LogManager.GetLogger(typeof(Program));
        public frmImportExcel()
        {
            InitializeComponent();

            this.txt_File.Text = @"D:\5月工资.xls";
            this.txt_Sheet.Text = "老板";
            this.txt_Cell.Text = "1,1";
            this.txt_Value.Text = "happy";
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //DataGridViewBind(@"D:\my.xls");
            try
            {
                string fileName, sheetName, cellvalue;
                int row, col;
                if (string.IsNullOrEmpty(this.txt_File.Text))
                {
                    throw new Exception("文件名空");
                }
                if (!File.Exists(this.txt_File.Text))
                {
                    throw new Exception("文件不存在");
                }
                if (string.IsNullOrEmpty(this.txt_Sheet.Text))
                {
                    throw new Exception("table名空");
                }
                if (string.IsNullOrEmpty(this.txt_Cell.Text))
                {
                    throw new Exception("cell名空");
                }
                else if (this.txt_Cell.Text.IndexOf(',') == -1)
                {
                    throw new Exception("cell ,隔开");
                }
                if (string.IsNullOrEmpty(this.txt_Value.Text))
                {
                    throw new Exception("vaue名空");
                }
                fileName = this.txt_File.Text;
                sheetName = this.txt_Sheet.Text;
                cellvalue = this.txt_Value.Text;             
                row = Int32.Parse(this.txt_Cell.Text.Split(',')[0]);
                col = Int32.Parse(this.txt_Cell.Text.Split(',')[1]);
                DataService.UpdateSheet(fileName,sheetName,row,col,cellvalue);
            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
                DataService.ShowErrorMsg(ex.Message);
            }
           
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.txt_Result.Text = "";
            try
            {
                string fileName, sheetName;
                int row, col;
                if (string.IsNullOrEmpty(this.txt_File.Text))
                {
                    throw new Exception("文件名空");
                }
                if (!File.Exists(this.txt_File.Text))
                {
                    throw new Exception("文件不存在");
                }
                if (string.IsNullOrEmpty(this.txt_Sheet.Text))
                {
                    throw new Exception("table名空");
                }
                if (string.IsNullOrEmpty(this.txt_Cell.Text))
                {
                    throw new Exception("cell名空");
                }
                else if (this.txt_Cell.Text.IndexOf(',') == -1)
                {
                    throw new Exception("cell ,隔开");
                }
                //if (string.IsNullOrEmpty(this.txt_Value.Text))
                //{
                //    throw new Exception("vaue名空");
                //}
                fileName = this.txt_File.Text;
                sheetName = this.txt_Sheet.Text;
                //cellvalue = this.txt_Value.Text;
                row = Int32.Parse(this.txt_Cell.Text.Split(',')[0]);
                col = Int32.Parse(this.txt_Cell.Text.Split(',')[1]);       
                //MessageBox.Show(result);
            }
            catch (Exception ex)
            {
                DataService.ShowErrorMsg(ex.Message);
            }
        }
        private void CheckValue()
        {
       
        }

        /// <summary>
        /// Excel数据导入方法
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="dgv"></param>
        public void EcxelToDataGridView(string filePath, DataGridView dgv)
        {
            //根据路径打开一个Excel文件并将数据填充到DataSet中
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//导入时包含Excel中的第一行数据，并且将数字和字符混合的单元格视为文本进行导入
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select  * from   [sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "table1");
           
        }


        /// <summary>
        /// Excel数据导入方法
        /// 作者:
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="dgv"></param>
        public void DataGridViewBind(string filePath)
        {
            //根据路径打开一个Excel文件并将数据填充到DataSet中
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//导入时包含Excel中的第一行数据，并且将数字和字符混合的单元格视为文本进行导入
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select  * from   [sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "table1");

            //根据DataGridView的列构造一个新的DataTable

            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                DataGridViewTextBoxColumn col = new DataGridViewTextBoxColumn();
                col.DataPropertyName = ds.Tables[0].Columns[i].ColumnName;
                col.HeaderText = ds.Tables[0].Columns[i].ColumnName;
                col.Name = ds.Tables[0].Columns[i].ColumnName;
                //col.ReadOnly = true;
                //col.Visible = false;s

                this.dataGridView1.Columns.Add(col);
            }
            this.dataGridView1.DataSource = ds.Tables[0];
            //this.dataGridView1.datab

        }

      
    }
}
